{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id='top'></a>\n",
    "# Extract Data for Final Plots of the Paper\n",
    "This noteboook will create the CSV files needed to generate the plots presented in the paper.\n",
    "\n",
    "Data files available:\n",
    "\n",
    "1. [Followers](#followers)\n",
    "1. [Bot score of connections](#botscore)\n",
    "1. [Ego Networks](#ego)\n",
    "1. [Exposure to echo chamber](#echo_chamber)\n",
    "1. [Exposure to low credibity content](#misinformation)\n",
    "1. [Political Valence and Algorithmic Bias](#political_valence)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "%load_ext autoreload\n",
    "%autoreload 2\n",
    "import sys\n",
    "sys.path.insert(1, '../exps/')\n",
    "import posgres_helper as db_helper\n",
    "sys.path.insert(1, '../metric/')\n",
    "import time_series_scores as ts_helper\n",
    "import plot_helper as plt_helper\n",
    "from collections import OrderedDict\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "from datetime import datetime as dt\n",
    "import matplotlib.pyplot as plt\n",
    "from matplotlib import dates\n",
    "import glob\n",
    "import networkx as nx\n",
    "import json\n",
    "import hashlib\n",
    "%matplotlib inline\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn = db_helper.connect_db()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Personalized data - NEED INPUT"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "GLOB_TO_EGO_NET = \"../data/to_delete/ego_network_graph/*.gexf\"\n",
    "\n",
    "BOTS_RENAME = OrderedDict()\n",
    "BOTS_RENAME[\"<DRIFTER_SCREENAME_1>\"] = \"bot1\"\n",
    "BOTS_RENAME[\"<DRIFTER_SCREENAME_2>\"] = \"bot2\"\n",
    "BOTS_RENAME[\"<DRIFTER_SCREENAME_3>\"] = \"bot3\"\n",
    "BOTS_RENAME[\"<DRIFTER_SCREENAME_4>\"] = \"bot4\"\n",
    "BOTS_RENAME[\"<DRIFTER_SCREENAME_5>\"] = \"bot5\"\n",
    "BOTS_RENAME[\"<DRIFTER_SCREENAME_6>\"] = \"bot6\"\n",
    "BOTS_RENAME[\"<DRIFTER_SCREENAME_7>\"] = \"bot7\"\n",
    "BOTS_RENAME[\"<DRIFTER_SCREENAME_8>\"] = \"bot8\"\n",
    "BOTS_RENAME[\"<DRIFTER_SCREENAME_9>\"] = \"bot9\"\n",
    "BOTS_RENAME[\"<DRIFTER_SCREENAME_10>\"] = \"bot10\"\n",
    "BOTS_RENAME[\"<DRIFTER_SCREENAME_11>\"] = \"bot11\"\n",
    "BOTS_RENAME[\"<DRIFTER_SCREENAME_12>\"] = \"bot12\"\n",
    "BOTS_RENAME[\"<DRIFTER_SCREENAME_13>\"] = \"bot13\"\n",
    "BOTS_RENAME[\"<DRIFTER_SCREENAME_14>\"] = \"bot14\"\n",
    "BOTS_RENAME[\"<DRIFTER_SCREENAME_15>\"] = \"bot15\"\n",
    "\n",
    "INIT_SEED_MAP = {\n",
    "  'thenation': ['<DRIFTER_SCREENAME_1>', '<DRIFTER_SCREENAME_2>', '<DRIFTER_SCREENAME_3>'],\n",
    "  'washingtonpost': ['<DRIFTER_SCREENAME_4>', '<DRIFTER_SCREENAME_5>', '<DRIFTER_SCREENAME_6>'],\n",
    "  'USATODAY': ['<DRIFTER_SCREENAME_7>', '<DRIFTER_SCREENAME_8>', '<DRIFTER_SCREENAME_9>'],\n",
    "  'WSJ': ['<DRIFTER_SCREENAME_10>', '<DRIFTER_SCREENAME_11>', '<DRIFTER_SCREENAME_12>'],\n",
    "  'BreitbartNews': ['<DRIFTER_SCREENAME_13>', '<DRIFTER_SCREENAME_14>', '<DRIFTER_SCREENAME_15>']\n",
    "}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[back to top](#top) <a id='followers'></a>\n",
    "## Followers           \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "followers_query = \"\"\"\n",
    "select b.screen_name, c.date, \n",
    "COUNT(c.t_usr_id_conn) from (\n",
    "   select distinct date_trunc('day', time) as date,\n",
    "                   t_usr_id_ego,\n",
    "                   conn_type, no_connctions,\n",
    "                   t_usr_id_conn from connections\n",
    ") as c\n",
    "inner join bot b on b.twitter_user_id= c.t_usr_id_ego\n",
    "where c.conn_type is true and c.no_connctions is false \n",
    "group by c.t_usr_id_ego, b.screen_name, date\n",
    "order by c.t_usr_id_ego, date;\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = db_helper.getDataframeFromQuery(conn, followers_query)\n",
    "df = df.pivot(index=\"date\",columns=\"screen_name\",values=\"count\")\n",
    "# anonymize bots\n",
    "df.columns = [BOTS_RENAME.get(c,c) for c in df.columns]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "DatetimeIndex: 168 entries, 2019-07-13 to 2020-01-14\n",
      "Data columns (total 15 columns):\n",
      "bot11    163 non-null float64\n",
      "bot14    163 non-null float64\n",
      "bot12    163 non-null float64\n",
      "bot13    163 non-null float64\n",
      "bot10    163 non-null float64\n",
      "bot7     164 non-null float64\n",
      "bot2     165 non-null float64\n",
      "bot5     163 non-null float64\n",
      "bot6     149 non-null float64\n",
      "bot15    162 non-null float64\n",
      "bot1     161 non-null float64\n",
      "bot4     163 non-null float64\n",
      "bot3     163 non-null float64\n",
      "bot8     163 non-null float64\n",
      "bot9     164 non-null float64\n",
      "dtypes: float64(15)\n",
      "memory usage: 21.0 KB\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>bot11</th>\n",
       "      <th>bot14</th>\n",
       "      <th>bot12</th>\n",
       "      <th>bot13</th>\n",
       "      <th>bot10</th>\n",
       "      <th>bot7</th>\n",
       "      <th>bot2</th>\n",
       "      <th>bot5</th>\n",
       "      <th>bot6</th>\n",
       "      <th>bot15</th>\n",
       "      <th>bot1</th>\n",
       "      <th>bot4</th>\n",
       "      <th>bot3</th>\n",
       "      <th>bot8</th>\n",
       "      <th>bot9</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2019-07-13</th>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-07-14</th>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-07-15</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            bot11  bot14  bot12  bot13  bot10  bot7  bot2  bot5  bot6  bot15  \\\n",
       "date                                                                           \n",
       "2019-07-13    1.0    NaN    1.0    3.0    NaN   1.0   NaN   NaN   NaN    1.0   \n",
       "2019-07-14    NaN    1.0    NaN    NaN    1.0   NaN   1.0   NaN   NaN    NaN   \n",
       "2019-07-15    NaN    NaN    NaN    NaN    NaN   NaN   2.0   NaN   NaN    NaN   \n",
       "\n",
       "            bot1  bot4  bot3  bot8  bot9  \n",
       "date                                      \n",
       "2019-07-13   NaN   2.0   1.0   1.0   NaN  \n",
       "2019-07-14   NaN   NaN   NaN   NaN   1.0  \n",
       "2019-07-15   NaN   NaN   NaN   NaN   NaN  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.info()\n",
    "# df[df.index<dt(2019,12,2)].to_csv(\"followers_data.csv\")\n",
    "df.to_csv(\"followers_data.csv\")\n",
    "df.head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[back to top](#top) \n",
    "<a id='botscore'></a>\n",
    "## Bot Score           \n",
    "To compute the botscore summary, we need to read a csv file containing a list of bot score for each friend and follower of the seeds and drifter bots.\n",
    "\n",
    "In this exercise, our input file is located in `data/connections_botscores.csv`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 1279 entries, 0 to 1278\n",
      "Data columns (total 40 columns):\n",
      "(BreitbartNews, friend)       119 non-null float64\n",
      "(BreitbartNews, follower)     396 non-null float64\n",
      "(bot11, friend)               287 non-null float64\n",
      "(bot11, follower)             115 non-null float64\n",
      "(bot14, friend)               308 non-null float64\n",
      "(bot14, follower)             155 non-null float64\n",
      "(bot12, friend)               263 non-null float64\n",
      "(bot12, follower)             117 non-null float64\n",
      "(bot13, friend)               265 non-null float64\n",
      "(bot13, follower)             155 non-null float64\n",
      "(bot10, friend)               250 non-null float64\n",
      "(bot10, follower)             65 non-null float64\n",
      "(USATODAY, friend)            325 non-null float64\n",
      "(USATODAY, follower)          413 non-null float64\n",
      "(WSJ, friend)                 366 non-null float64\n",
      "(WSJ, follower)               445 non-null float64\n",
      "(bot7, friend)                237 non-null float64\n",
      "(bot7, follower)              39 non-null float64\n",
      "(bot2, friend)                260 non-null float64\n",
      "(bot2, follower)              89 non-null float64\n",
      "(bot5, friend)                254 non-null float64\n",
      "(bot5, follower)              107 non-null float64\n",
      "(bot6, friend)                257 non-null float64\n",
      "(bot6, follower)              38 non-null float64\n",
      "(bot15, friend)               304 non-null float64\n",
      "(bot15, follower)             105 non-null float64\n",
      "(bot1, friend)                244 non-null float64\n",
      "(bot1, follower)              47 non-null float64\n",
      "(thenation, friend)           526 non-null float64\n",
      "(thenation, follower)         348 non-null float64\n",
      "(bot4, friend)                226 non-null float64\n",
      "(bot4, follower)              20 non-null float64\n",
      "(bot3, friend)                262 non-null float64\n",
      "(bot3, follower)              106 non-null float64\n",
      "(bot8, friend)                241 non-null float64\n",
      "(bot8, follower)              32 non-null float64\n",
      "(washingtonpost, friend)      486 non-null float64\n",
      "(washingtonpost, follower)    376 non-null float64\n",
      "(bot9, friend)                270 non-null float64\n",
      "(bot9, follower)              36 non-null float64\n",
      "dtypes: float64(40)\n",
      "memory usage: 409.7 KB\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th>t_usr_id_ego</th>\n",
       "      <th colspan=\"2\" halign=\"left\">BreitbartNews</th>\n",
       "      <th colspan=\"2\" halign=\"left\">bot11</th>\n",
       "      <th colspan=\"2\" halign=\"left\">bot14</th>\n",
       "      <th colspan=\"2\" halign=\"left\">bot12</th>\n",
       "      <th colspan=\"2\" halign=\"left\">bot13</th>\n",
       "      <th>...</th>\n",
       "      <th colspan=\"2\" halign=\"left\">bot4</th>\n",
       "      <th colspan=\"2\" halign=\"left\">bot3</th>\n",
       "      <th colspan=\"2\" halign=\"left\">bot8</th>\n",
       "      <th colspan=\"2\" halign=\"left\">washingtonpost</th>\n",
       "      <th colspan=\"2\" halign=\"left\">bot9</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>conn_type</th>\n",
       "      <th>friend</th>\n",
       "      <th>follower</th>\n",
       "      <th>friend</th>\n",
       "      <th>follower</th>\n",
       "      <th>friend</th>\n",
       "      <th>follower</th>\n",
       "      <th>friend</th>\n",
       "      <th>follower</th>\n",
       "      <th>friend</th>\n",
       "      <th>follower</th>\n",
       "      <th>...</th>\n",
       "      <th>friend</th>\n",
       "      <th>follower</th>\n",
       "      <th>friend</th>\n",
       "      <th>follower</th>\n",
       "      <th>friend</th>\n",
       "      <th>follower</th>\n",
       "      <th>friend</th>\n",
       "      <th>follower</th>\n",
       "      <th>friend</th>\n",
       "      <th>follower</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.049184</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.067835</td>\n",
       "      <td>0.813872</td>\n",
       "      <td>0.421517</td>\n",
       "      <td>0.706420</td>\n",
       "      <td>0.193749</td>\n",
       "      <td>0.067835</td>\n",
       "      <td>0.506192</td>\n",
       "      <td>0.590513</td>\n",
       "      <td>...</td>\n",
       "      <td>0.025472</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.569732</td>\n",
       "      <td>0.025472</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.027678</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.027678</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.193749</td>\n",
       "      <td>0.125889</td>\n",
       "      <td>0.032660</td>\n",
       "      <td>0.421517</td>\n",
       "      <td>0.085924</td>\n",
       "      <td>0.236884</td>\n",
       "      <td>0.030069</td>\n",
       "      <td>0.108277</td>\n",
       "      <td>...</td>\n",
       "      <td>0.079455</td>\n",
       "      <td>0.116795</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.484865</td>\n",
       "      <td>0.023437</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.025472</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.045344</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.023437</td>\n",
       "      <td>0.236884</td>\n",
       "      <td>0.421517</td>\n",
       "      <td>0.193749</td>\n",
       "      <td>0.590513</td>\n",
       "      <td>0.569732</td>\n",
       "      <td>0.341219</td>\n",
       "      <td>0.221806</td>\n",
       "      <td>...</td>\n",
       "      <td>0.067835</td>\n",
       "      <td>0.341219</td>\n",
       "      <td>0.057807</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.669827</td>\n",
       "      <td>0.380552</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.035466</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3 rows × 40 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "t_usr_id_ego BreitbartNews              bot11               bot14            \\\n",
       "conn_type           friend follower    friend  follower    friend  follower   \n",
       "0                 0.049184      NaN  0.067835  0.813872  0.421517  0.706420   \n",
       "1                 0.027678      NaN  0.193749  0.125889  0.032660  0.421517   \n",
       "2                 0.045344      NaN  0.023437  0.236884  0.421517  0.193749   \n",
       "\n",
       "t_usr_id_ego     bot12               bot13            ...      bot4            \\\n",
       "conn_type       friend  follower    friend  follower  ...    friend  follower   \n",
       "0             0.193749  0.067835  0.506192  0.590513  ...  0.025472       NaN   \n",
       "1             0.085924  0.236884  0.030069  0.108277  ...  0.079455  0.116795   \n",
       "2             0.590513  0.569732  0.341219  0.221806  ...  0.067835  0.341219   \n",
       "\n",
       "t_usr_id_ego      bot3                bot8           washingtonpost           \\\n",
       "conn_type       friend  follower    friend  follower         friend follower   \n",
       "0                  NaN  0.569732  0.025472       NaN       0.027678      NaN   \n",
       "1                  NaN  0.484865  0.023437       NaN            NaN      NaN   \n",
       "2             0.057807       NaN  0.669827  0.380552            NaN      NaN   \n",
       "\n",
       "t_usr_id_ego      bot9           \n",
       "conn_type       friend follower  \n",
       "0                  NaN      NaN  \n",
       "1             0.025472      NaN  \n",
       "2             0.035466      NaN  \n",
       "\n",
       "[3 rows x 40 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "connections_overall_botscores = pd.read_csv(\n",
    "    \"connections_botscores.csv\",\n",
    "    index_col=0, \n",
    "    header=[0, 1], \n",
    "    skipinitialspace=True\n",
    ")\n",
    "connections_overall_botscores.info()\n",
    "\n",
    "overall_botscore={}\n",
    "colors_for_seeds={}\n",
    "conn_botscore = []\n",
    "for relationship in [\"friend\",\"follower\"]:\n",
    "    \n",
    "    for seed in plt_helper.INIT_SEED_MAP.keys():\n",
    "        overall_botscore[plt_helper.INIT_SEED_RENAME.get(seed)] = np.concatenate(\n",
    "            connections_overall_botscores.swaplevel(axis=1)[relationship][plt_helper.INIT_SEED_MAP[seed]].values\n",
    "        )\n",
    "        colors_for_seeds[plt_helper.INIT_SEED_RENAME.get(seed)] = (plt_helper.ACCOUNT_COLORS.get(seed))\n",
    "\n",
    "    overall_botscore = pd.DataFrame(overall_botscore)[plt_helper.INIT_SEED_RENAME.values()]\n",
    "    tmp = overall_botscore.apply([\"mean\",\"std\",\"sem\"]).transpose()\n",
    "    tmp[\"relationship\"] = relationship\n",
    "    conn_botscore.append(tmp.reset_index().groupby([\"relationship\",\"index\"]).agg(\"mean\"))\n",
    "\n",
    "connections_overall_botscores.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>mean</th>\n",
       "      <th>std</th>\n",
       "      <th>sem</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>relationship</th>\n",
       "      <th>index</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">friend</th>\n",
       "      <th>Center</th>\n",
       "      <td>0.169657</td>\n",
       "      <td>0.249306</td>\n",
       "      <td>0.009116</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Center-left</th>\n",
       "      <td>0.203355</td>\n",
       "      <td>0.254144</td>\n",
       "      <td>0.009362</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Center-right</th>\n",
       "      <td>0.256564</td>\n",
       "      <td>0.262230</td>\n",
       "      <td>0.009271</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Left</th>\n",
       "      <td>0.268681</td>\n",
       "      <td>0.271701</td>\n",
       "      <td>0.009817</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Right</th>\n",
       "      <td>0.315996</td>\n",
       "      <td>0.261187</td>\n",
       "      <td>0.008820</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">follower</th>\n",
       "      <th>Center</th>\n",
       "      <td>0.505729</td>\n",
       "      <td>0.319875</td>\n",
       "      <td>0.030923</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Center-left</th>\n",
       "      <td>0.463571</td>\n",
       "      <td>0.285241</td>\n",
       "      <td>0.022206</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Center-right</th>\n",
       "      <td>0.436814</td>\n",
       "      <td>0.263265</td>\n",
       "      <td>0.015276</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Left</th>\n",
       "      <td>0.463661</td>\n",
       "      <td>0.265377</td>\n",
       "      <td>0.017059</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Right</th>\n",
       "      <td>0.436640</td>\n",
       "      <td>0.238697</td>\n",
       "      <td>0.011717</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                               mean       std       sem\n",
       "relationship index                                     \n",
       "friend       Center        0.169657  0.249306  0.009116\n",
       "             Center-left   0.203355  0.254144  0.009362\n",
       "             Center-right  0.256564  0.262230  0.009271\n",
       "             Left          0.268681  0.271701  0.009817\n",
       "             Right         0.315996  0.261187  0.008820\n",
       "follower     Center        0.505729  0.319875  0.030923\n",
       "             Center-left   0.463571  0.285241  0.022206\n",
       "             Center-right  0.436814  0.263265  0.015276\n",
       "             Left          0.463661  0.265377  0.017059\n",
       "             Right         0.436640  0.238697  0.011717"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "botscore_summary = pd.concat(conn_botscore)\n",
    "botscore_summary.to_csv(\"bot_score_summary.csv\")\n",
    "botscore_summary"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[back to top](#top) \n",
    "<a id='ego'></a>\n",
    "## Creating Anonimmized Ego Networks\n",
    "To create the anonimmized ego networks with the respective link and hashtag scores, we need the real (no hashed) ego network as input. The method below will:\n",
    "\n",
    "1. access the data base to compute the link and hashtag scores.\n",
    "1. anonimize the nodes.\n",
    "1. save the anonimized ego networks."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "def add_edges_to_bot(g, bot_id=\"bot\"):\n",
    "    for i in list(g.nodes()):\n",
    "        if bot_id != i:\n",
    "            g.add_edge(bot_id,i)\n",
    "    return g\n",
    "\n",
    "def hash_user_id(twitter_user_id):\n",
    "    return int(hashlib.sha1(twitter_user_id.encode('utf-8')).hexdigest(), 16) % (10 ** 8)\n",
    "\n",
    "def clean_node_attributes(graph):\n",
    "    clean_graph = nx.from_edgelist(graph.edges)\n",
    "    for id, attr in pd.DataFrame(\n",
    "                        dict(graph.nodes(data=True)).values(),\n",
    "                        index=dict(graph.nodes(data=True)).keys()\n",
    "                    ).dropna(\n",
    "                        axis=\"columns\", \n",
    "                        how=\"all\"\n",
    "                    ).iterrows():\n",
    "        clean_graph.add_node(id, **attr.dropna().to_dict())\n",
    "    return clean_graph\n",
    "\n",
    "def anonymize_graph(graph):\n",
    "    nx.relabel_nodes(graph, lambda x: hash_user_id(x), copy=False)\n",
    "    nx.set_node_attributes(\n",
    "        graph,\n",
    "        values={k:(v[\"label\"] if \"bot\" in v[\"label\"] else k) for k,v in graph.nodes(data=True)},\n",
    "        name=\"label\"\n",
    "    )\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "bots = db_helper.getDataframeFromQuery(db_helper.connect_db(), \"select screen_name, twitter_user_id, seed_screen_name from bot;\")\n",
    "bots.seed_screen_name = bots.seed_screen_name.apply(plt_helper.INIT_SEED_RENAME.get)\n",
    "bots[\"mask_name\"] = bots.screen_name.apply(BOTS_RENAME.get)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1476"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "total_graph = nx.Graph()\n",
    "for filename in glob.glob(GLOB_TO_EGO_NET):\n",
    "    bot_name = filename.split(\"/\")[-1].replace(\"_noBotNoHash.gexf\",\"\")\n",
    "    seed = plt_helper.INIT_SEED_RENAME.get(plt_helper.BOT_SEED_MAP.get(BOTS_RENAME.get(bot_name)))\n",
    "    graph = nx.read_gexf(filename)\n",
    "    \n",
    "    ## adding bot to the network\n",
    "    bot_profile = bots[bots.screen_name==bot_name].iloc[0]\n",
    "    nx.set_node_attributes(graph,values=True,name=bot_profile.mask_name)\n",
    "    graph.add_node(\n",
    "        bot_profile.twitter_user_id, \n",
    "        seed=bot_profile.seed_screen_name, \n",
    "        label=bot_profile.mask_name,\n",
    "#         **{bot_profile.mask_name:True}\n",
    "    )\n",
    "    add_edges_to_bot(graph, bot_id=bot_profile.twitter_user_id)\n",
    "    \n",
    "    total_graph = nx.compose(total_graph, graph)\n",
    "total_graph.number_of_nodes()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Index: 1383 entries, 1000599852936396800 to 99806132\n",
      "Data columns (total 3 columns):\n",
      "url_score         919 non-null float64\n",
      "hashtag_score     1069 non-null float64\n",
      "low_cred_score    1175 non-null float64\n",
      "dtypes: float64(3)\n",
      "memory usage: 43.2+ KB\n"
     ]
    }
   ],
   "source": [
    "homo_df = db_helper.getDataframeFromQuery(\n",
    "    db_helper.connect_db(),\n",
    "    \"\"\"\n",
    "select \n",
    "    user_id,\n",
    "    avg(url_score) as url_score,\n",
    "    avg(hashtag_score) as hashtag_score,\n",
    "    sum(low_cred_score) as low_cred_score\n",
    "from \n",
    "    tweet \n",
    "where \n",
    "    user_id in {}\n",
    "group by\n",
    "    user_id\n",
    ";\"\"\".format(tuple(total_graph.nodes))\n",
    ")\n",
    "\n",
    "homo_df = homo_df.set_index(\"user_id\").apply(pd.to_numeric)\n",
    "# adjust hashtag score to center at zero\n",
    "homo_df.hashtag_score = homo_df.hashtag_score.apply(lambda x: x - plt_helper.USATODAY_HASHTAG_SCORE if x else x)\n",
    "homo_df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "for k,v in homo_df.to_dict(orient=\"index\").items():\n",
    "    total_graph.add_node(k, **v)\n",
    "\n",
    "homo_df = pd.DataFrame.from_dict(dict(total_graph.nodes(data=True)), orient='index')\n",
    "\n",
    "anonymize_graph(total_graph)\n",
    "nx.write_graphml(clean_node_attributes(total_graph),\"./ego_networks/homogeneity_network.graphml\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "for filename in glob.glob(GLOB_TO_EGO_NET):\n",
    "    bot_name = filename.split(\"/\")[-1].replace(\"_noBotNoHash.gexf\",\"\")\n",
    "    seed = plt_helper.INIT_SEED_RENAME.get(plt_helper.BOT_SEED_MAP.get(BOTS_RENAME.get(bot_name)))\n",
    "    graph = nx.read_gexf(filename)\n",
    "    \n",
    "    ## adding bot to the network\n",
    "    bot_profile = bots[bots.screen_name==bot_name].iloc[0]\n",
    "    nx.set_node_attributes(graph,values=True,name=bot_profile.mask_name)\n",
    "    graph.add_node(\n",
    "        bot_profile.twitter_user_id, \n",
    "        seed=bot_profile.seed_screen_name, \n",
    "        label=bot_profile.mask_name,\n",
    "#         **{bot_profile.mask_name:True}\n",
    "    )\n",
    "    add_edges_to_bot(graph, bot_id=bot_profile.twitter_user_id)\n",
    "    \n",
    "    # adding scores to nodes\n",
    "    for node in graph.nodes:\n",
    "        if node in homo_df.index:\n",
    "            graph.add_node(node, **homo_df.loc[node].to_dict())\n",
    "        \n",
    "    # rename nodes ids and labels to preserve anonymity \n",
    "    anonymize_graph(graph)\n",
    "\n",
    "    nx.write_gexf(clean_node_attributes(graph),\"./ego_networks/ego_network_with_scores-{}.gexf\".format(bot_profile.mask_name))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[back to top](#top) \n",
    "<a id='echo_chamber'></a>\n",
    "## Echo Chambers\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "results = []\n",
    "for filename in glob.glob(\"./ego_networks/ego_network_with_scores-*.gexf\"):\n",
    "    bot_name = filename.split(\"/\")[-1].replace(\".gexf\",\"\").replace(\"ego_network_with_scores-\",\"\")\n",
    "    seed = plt_helper.INIT_SEED_RENAME.get(plt_helper.BOT_SEED_MAP.get(bot_name))\n",
    "    graph = nx.read_gexf(filename)\n",
    "\n",
    "    columns = [\"trans\",\"density\",\"avg_clus\",\"edges\",\"nodes\"]\n",
    "#     graph = add_edges_to_bot(graph)\n",
    "    bot_res = pd.Series(\n",
    "        (nx.transitivity(graph),nx.density(graph), nx.average_clustering(graph),len(graph.edges),len(graph.nodes)),\n",
    "        index = columns\n",
    "    )\n",
    "    bot_res[\"bot\"] = bot_name\n",
    "    bot_res[\"seed\"] = seed\n",
    "    \n",
    "    # creating new graph shuffling the edges\n",
    "    n,d = zip(*list(graph.degree))\n",
    "    \n",
    "    trial=[]\n",
    "    for i in range(30):\n",
    "#         graph2 = nx.gnm_random_graph(len(graph.nodes), len(graph.edges))\n",
    "        graph2 = nx.configuration_model(d, create_using=nx.Graph)\n",
    "        graph2.remove_edges_from(nx.selfloop_edges(graph2))\n",
    "        while len(graph.edges) - len(graph2.edges):\n",
    "            source = pd.Series(graph2.nodes).sample(1).values[0]\n",
    "            neighbors = list(nx.neighbors(graph2,source)) + [source]\n",
    "            target = pd.Series(\n",
    "                np.setdiff1d(graph2.nodes, neighbors)\n",
    "            ).sample(1).values[0]\n",
    "            graph2.add_edge(source, target)\n",
    "        trial.append(\n",
    "            (nx.transitivity(graph2),nx.density(graph2), nx.average_clustering(graph2),len(graph2.edges),len(graph2.nodes)  )\n",
    "        )\n",
    "    bot_rand_res = pd.Series(\n",
    "        pd.DataFrame(trial).apply(\"mean\").values,\n",
    "        index = [f\"{c}_rand\" for c in columns]\n",
    "    )\n",
    "    \n",
    "    results.append(pd.concat([bot_res, bot_rand_res]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>seed</th>\n",
       "      <th>Left</th>\n",
       "      <th>Center-left</th>\n",
       "      <th>Center</th>\n",
       "      <th>Center-right</th>\n",
       "      <th>Right</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">avg_clus</th>\n",
       "      <th>mean</th>\n",
       "      <td>0.563835</td>\n",
       "      <td>0.548106</td>\n",
       "      <td>0.563015</td>\n",
       "      <td>0.573352</td>\n",
       "      <td>0.577113</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>sem</th>\n",
       "      <td>0.020577</td>\n",
       "      <td>0.041300</td>\n",
       "      <td>0.008027</td>\n",
       "      <td>0.008723</td>\n",
       "      <td>0.013674</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">avg_clus_rand</th>\n",
       "      <th>mean</th>\n",
       "      <td>0.213801</td>\n",
       "      <td>0.204955</td>\n",
       "      <td>0.174262</td>\n",
       "      <td>0.273524</td>\n",
       "      <td>0.282133</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>sem</th>\n",
       "      <td>0.014089</td>\n",
       "      <td>0.019870</td>\n",
       "      <td>0.002916</td>\n",
       "      <td>0.015414</td>\n",
       "      <td>0.012559</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">density</th>\n",
       "      <th>mean</th>\n",
       "      <td>0.084752</td>\n",
       "      <td>0.073993</td>\n",
       "      <td>0.050825</td>\n",
       "      <td>0.141056</td>\n",
       "      <td>0.149241</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>sem</th>\n",
       "      <td>0.012279</td>\n",
       "      <td>0.023568</td>\n",
       "      <td>0.003140</td>\n",
       "      <td>0.015186</td>\n",
       "      <td>0.011465</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">density_rand</th>\n",
       "      <th>mean</th>\n",
       "      <td>0.084752</td>\n",
       "      <td>0.073993</td>\n",
       "      <td>0.050825</td>\n",
       "      <td>0.141056</td>\n",
       "      <td>0.149241</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>sem</th>\n",
       "      <td>0.012279</td>\n",
       "      <td>0.023568</td>\n",
       "      <td>0.003140</td>\n",
       "      <td>0.015186</td>\n",
       "      <td>0.011465</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">edges</th>\n",
       "      <th>mean</th>\n",
       "      <td>428.000000</td>\n",
       "      <td>373.666667</td>\n",
       "      <td>256.666667</td>\n",
       "      <td>712.333333</td>\n",
       "      <td>753.666667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>sem</th>\n",
       "      <td>62.010752</td>\n",
       "      <td>119.020073</td>\n",
       "      <td>15.857000</td>\n",
       "      <td>76.690576</td>\n",
       "      <td>57.897419</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">edges_rand</th>\n",
       "      <th>mean</th>\n",
       "      <td>428.000000</td>\n",
       "      <td>373.666667</td>\n",
       "      <td>256.666667</td>\n",
       "      <td>712.333333</td>\n",
       "      <td>753.666667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>sem</th>\n",
       "      <td>62.010752</td>\n",
       "      <td>119.020073</td>\n",
       "      <td>15.857000</td>\n",
       "      <td>76.690576</td>\n",
       "      <td>57.897419</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">nodes</th>\n",
       "      <th>mean</th>\n",
       "      <td>101.000000</td>\n",
       "      <td>101.000000</td>\n",
       "      <td>101.000000</td>\n",
       "      <td>101.000000</td>\n",
       "      <td>101.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>sem</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">nodes_rand</th>\n",
       "      <th>mean</th>\n",
       "      <td>101.000000</td>\n",
       "      <td>101.000000</td>\n",
       "      <td>101.000000</td>\n",
       "      <td>101.000000</td>\n",
       "      <td>101.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>sem</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">trans</th>\n",
       "      <th>mean</th>\n",
       "      <td>0.278373</td>\n",
       "      <td>0.213865</td>\n",
       "      <td>0.117176</td>\n",
       "      <td>0.452916</td>\n",
       "      <td>0.428614</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>sem</th>\n",
       "      <td>0.041462</td>\n",
       "      <td>0.081745</td>\n",
       "      <td>0.018987</td>\n",
       "      <td>0.022816</td>\n",
       "      <td>0.027530</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">trans_density_norm</th>\n",
       "      <th>mean</th>\n",
       "      <td>3.325911</td>\n",
       "      <td>2.772386</td>\n",
       "      <td>2.275590</td>\n",
       "      <td>3.299401</td>\n",
       "      <td>2.881191</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>sem</th>\n",
       "      <td>0.369730</td>\n",
       "      <td>0.228593</td>\n",
       "      <td>0.242467</td>\n",
       "      <td>0.441284</td>\n",
       "      <td>0.117286</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">trans_rand</th>\n",
       "      <th>mean</th>\n",
       "      <td>0.199564</td>\n",
       "      <td>0.164397</td>\n",
       "      <td>0.115822</td>\n",
       "      <td>0.299038</td>\n",
       "      <td>0.292392</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>sem</th>\n",
       "      <td>0.018590</td>\n",
       "      <td>0.037092</td>\n",
       "      <td>0.012320</td>\n",
       "      <td>0.011256</td>\n",
       "      <td>0.018270</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">trans_rand_norm</th>\n",
       "      <th>mean</th>\n",
       "      <td>1.385340</td>\n",
       "      <td>1.213718</td>\n",
       "      <td>0.998560</td>\n",
       "      <td>1.516162</td>\n",
       "      <td>1.465555</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>sem</th>\n",
       "      <td>0.115605</td>\n",
       "      <td>0.194549</td>\n",
       "      <td>0.061739</td>\n",
       "      <td>0.071313</td>\n",
       "      <td>0.004836</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "seed                           Left  Center-left      Center  Center-right  \\\n",
       "avg_clus           mean    0.563835     0.548106    0.563015      0.573352   \n",
       "                   sem     0.020577     0.041300    0.008027      0.008723   \n",
       "avg_clus_rand      mean    0.213801     0.204955    0.174262      0.273524   \n",
       "                   sem     0.014089     0.019870    0.002916      0.015414   \n",
       "density            mean    0.084752     0.073993    0.050825      0.141056   \n",
       "                   sem     0.012279     0.023568    0.003140      0.015186   \n",
       "density_rand       mean    0.084752     0.073993    0.050825      0.141056   \n",
       "                   sem     0.012279     0.023568    0.003140      0.015186   \n",
       "edges              mean  428.000000   373.666667  256.666667    712.333333   \n",
       "                   sem    62.010752   119.020073   15.857000     76.690576   \n",
       "edges_rand         mean  428.000000   373.666667  256.666667    712.333333   \n",
       "                   sem    62.010752   119.020073   15.857000     76.690576   \n",
       "nodes              mean  101.000000   101.000000  101.000000    101.000000   \n",
       "                   sem     0.000000     0.000000    0.000000      0.000000   \n",
       "nodes_rand         mean  101.000000   101.000000  101.000000    101.000000   \n",
       "                   sem     0.000000     0.000000    0.000000      0.000000   \n",
       "trans              mean    0.278373     0.213865    0.117176      0.452916   \n",
       "                   sem     0.041462     0.081745    0.018987      0.022816   \n",
       "trans_density_norm mean    3.325911     2.772386    2.275590      3.299401   \n",
       "                   sem     0.369730     0.228593    0.242467      0.441284   \n",
       "trans_rand         mean    0.199564     0.164397    0.115822      0.299038   \n",
       "                   sem     0.018590     0.037092    0.012320      0.011256   \n",
       "trans_rand_norm    mean    1.385340     1.213718    0.998560      1.516162   \n",
       "                   sem     0.115605     0.194549    0.061739      0.071313   \n",
       "\n",
       "seed                          Right  \n",
       "avg_clus           mean    0.577113  \n",
       "                   sem     0.013674  \n",
       "avg_clus_rand      mean    0.282133  \n",
       "                   sem     0.012559  \n",
       "density            mean    0.149241  \n",
       "                   sem     0.011465  \n",
       "density_rand       mean    0.149241  \n",
       "                   sem     0.011465  \n",
       "edges              mean  753.666667  \n",
       "                   sem    57.897419  \n",
       "edges_rand         mean  753.666667  \n",
       "                   sem    57.897419  \n",
       "nodes              mean  101.000000  \n",
       "                   sem     0.000000  \n",
       "nodes_rand         mean  101.000000  \n",
       "                   sem     0.000000  \n",
       "trans              mean    0.428614  \n",
       "                   sem     0.027530  \n",
       "trans_density_norm mean    2.881191  \n",
       "                   sem     0.117286  \n",
       "trans_rand         mean    0.292392  \n",
       "                   sem     0.018270  \n",
       "trans_rand_norm    mean    1.465555  \n",
       "                   sem     0.004836  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_res = pd.DataFrame(results)\n",
    "final_res[\"trans_rand_norm\"] = final_res.trans/final_res.trans_rand\n",
    "final_res[\"trans_density_norm\"] = final_res.trans/final_res.density\n",
    "final_res = final_res[final_res.columns.sort_values()]\n",
    "\n",
    "final_res_grp = final_res.groupby(\"seed\").agg([\"mean\",\"sem\"]).transpose()\n",
    "final_res_grp = final_res_grp[[c for c in plt_helper.ACCOUNT_COLORS.keys() if c in final_res_grp.columns]]\n",
    "final_res_grp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "final_res_grp.to_csv(\"echo_chamber_data.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[back to top](#top) <a id='misinformation'></a>\n",
    "## Low Credibility           \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 317425 entries, 0 to 317424\n",
      "Data columns (total 5 columns):\n",
      "screen_name       317425 non-null object\n",
      "seed              317425 non-null object\n",
      "tweet_id          317425 non-null object\n",
      "low_cred_score    62935 non-null float64\n",
      "checked_at        317425 non-null datetime64[ns]\n",
      "dtypes: datetime64[ns](1), float64(1), object(3)\n",
      "memory usage: 12.1+ MB\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/u/pacheco/.local/lib/python3.6/site-packages/pandas/core/generic.py:3946: PerformanceWarning: dropping on a non-lexsorted multi-index without a level parameter may impact performance.\n",
      "  new_axis = axis.drop(labels, errors=errors)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"9\" halign=\"left\">low_cred_score</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"3\" halign=\"left\">sum</th>\n",
       "      <th colspan=\"3\" halign=\"left\">mean</th>\n",
       "      <th colspan=\"3\" halign=\"left\">count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>sum</th>\n",
       "      <th>mean</th>\n",
       "      <th>sem</th>\n",
       "      <th>sum</th>\n",
       "      <th>mean</th>\n",
       "      <th>sem</th>\n",
       "      <th>sum</th>\n",
       "      <th>mean</th>\n",
       "      <th>sem</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>seed</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Center</th>\n",
       "      <td>128.0</td>\n",
       "      <td>42.666667</td>\n",
       "      <td>18.800118</td>\n",
       "      <td>0.026115</td>\n",
       "      <td>0.008705</td>\n",
       "      <td>0.003626</td>\n",
       "      <td>13716</td>\n",
       "      <td>4572.000000</td>\n",
       "      <td>1036.289696</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Center-left</th>\n",
       "      <td>162.0</td>\n",
       "      <td>54.000000</td>\n",
       "      <td>43.139309</td>\n",
       "      <td>0.029846</td>\n",
       "      <td>0.009949</td>\n",
       "      <td>0.007425</td>\n",
       "      <td>13625</td>\n",
       "      <td>4541.666667</td>\n",
       "      <td>813.457094</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Center-right</th>\n",
       "      <td>987.0</td>\n",
       "      <td>329.000000</td>\n",
       "      <td>78.117433</td>\n",
       "      <td>0.292726</td>\n",
       "      <td>0.097575</td>\n",
       "      <td>0.006896</td>\n",
       "      <td>10266</td>\n",
       "      <td>3422.000000</td>\n",
       "      <td>924.730772</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Left</th>\n",
       "      <td>114.0</td>\n",
       "      <td>38.000000</td>\n",
       "      <td>24.542480</td>\n",
       "      <td>0.029712</td>\n",
       "      <td>0.009904</td>\n",
       "      <td>0.008004</td>\n",
       "      <td>17461</td>\n",
       "      <td>5820.333333</td>\n",
       "      <td>1245.954297</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Right</th>\n",
       "      <td>1129.0</td>\n",
       "      <td>376.333333</td>\n",
       "      <td>66.237787</td>\n",
       "      <td>0.434573</td>\n",
       "      <td>0.144858</td>\n",
       "      <td>0.004100</td>\n",
       "      <td>7867</td>\n",
       "      <td>2622.333333</td>\n",
       "      <td>511.244995</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             low_cred_score                                             \\\n",
       "                        sum                             mean             \n",
       "                        sum        mean        sem       sum      mean   \n",
       "seed                                                                     \n",
       "Center                128.0   42.666667  18.800118  0.026115  0.008705   \n",
       "Center-left           162.0   54.000000  43.139309  0.029846  0.009949   \n",
       "Center-right          987.0  329.000000  78.117433  0.292726  0.097575   \n",
       "Left                  114.0   38.000000  24.542480  0.029712  0.009904   \n",
       "Right                1129.0  376.333333  66.237787  0.434573  0.144858   \n",
       "\n",
       "                                                         \n",
       "                        count                            \n",
       "                   sem    sum         mean          sem  \n",
       "seed                                                     \n",
       "Center        0.003626  13716  4572.000000  1036.289696  \n",
       "Center-left   0.007425  13625  4541.666667   813.457094  \n",
       "Center-right  0.006896  10266  3422.000000   924.730772  \n",
       "Left          0.008004  17461  5820.333333  1245.954297  \n",
       "Right         0.004100   7867  2622.333333   511.244995  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "start_date = \"2019-04-01\"\n",
    "end_date = \"2019-12-02\"\n",
    "test=\"\"\"\n",
    "SELECT\n",
    "    DISTINCT b.screen_name, \n",
    "    b.seed_screen_name as seed,\n",
    "    tw.tweet_id,\n",
    "    tw.low_cred_score,\n",
    "    DATE(checked_at) checked_at\n",
    "FROM\n",
    "    home_timeline ht, home_timeline_tweets ht_tw, tweet tw, bot b\n",
    "WHERE\n",
    "    checked_at >= DATE '{}'\n",
    "    AND checked_at < DATE '{}'\n",
    "    AND ht.id = ht_tw.htl_id\n",
    "    AND ht_tw.tw_id = tw.tweet_id\n",
    "    AND ht.bot_id = b.bot_id\n",
    ";\n",
    "\"\"\"\n",
    "low_cred_tw = db_helper.getDataframeFromQuery(conn, test.format(start_date, end_date))\n",
    "low_cred_tw.checked_at = pd.to_datetime(low_cred_tw.checked_at)\n",
    "low_cred_tw.low_cred_score = low_cred_tw.low_cred_score.astype(\"double\")\n",
    "low_cred_tw.info()\n",
    "\n",
    "low_cred_summary = low_cred_tw.groupby([\"screen_name\",\"seed\"]).agg([\"sum\",\"mean\",\"count\"]).reset_index()\n",
    "low_cred_summary[\"seed\"] = low_cred_summary.seed.apply(\n",
    "    plt_helper.INIT_SEED_RENAME.get\n",
    ")\n",
    "low_cred_summary = low_cred_summary.groupby(\"seed\").agg([\"sum\",\"mean\",\"sem\"])\n",
    "low_cred_summary"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "low_cred_summary.to_csv(\"low_credibility_summary_data.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[back to top](#top) <a id='political_valence'></a>\n",
    "## Political Valence and Algorithmic Bias\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "url bot1 url_thenation_sliced_home_tl\n",
      "url bot2 url_thenation_sliced_home_tl\n",
      "url bot3 url_thenation_sliced_home_tl\n",
      "url bot1 url_thenation_sliced_usr_tl\n",
      "url bot2 url_thenation_sliced_usr_tl\n",
      "url bot3 url_thenation_sliced_usr_tl\n",
      "url bot1 url_thenation_sliced_friend_usr_tl\n",
      "url bot2 url_thenation_sliced_friend_usr_tl\n",
      "url bot3 url_thenation_sliced_friend_usr_tl\n",
      "hashtag bot1 hashtag_thenation_sliced_home_tl\n",
      "hashtag bot2 hashtag_thenation_sliced_home_tl\n",
      "hashtag bot3 hashtag_thenation_sliced_home_tl\n",
      "hashtag bot1 hashtag_thenation_sliced_usr_tl\n",
      "hashtag bot2 hashtag_thenation_sliced_usr_tl\n",
      "hashtag bot3 hashtag_thenation_sliced_usr_tl\n",
      "hashtag bot1 hashtag_thenation_sliced_friend_usr_tl\n",
      "hashtag bot2 hashtag_thenation_sliced_friend_usr_tl\n",
      "hashtag bot3 hashtag_thenation_sliced_friend_usr_tl\n",
      "url bot4 url_washingtonpost_sliced_home_tl\n",
      "url bot5 url_washingtonpost_sliced_home_tl\n",
      "url bot6 url_washingtonpost_sliced_home_tl\n",
      "url bot4 url_washingtonpost_sliced_usr_tl\n",
      "url bot5 url_washingtonpost_sliced_usr_tl\n",
      "url bot6 url_washingtonpost_sliced_usr_tl\n",
      "url bot4 url_washingtonpost_sliced_friend_usr_tl\n",
      "url bot5 url_washingtonpost_sliced_friend_usr_tl\n",
      "url bot6 url_washingtonpost_sliced_friend_usr_tl\n",
      "hashtag bot4 hashtag_washingtonpost_sliced_home_tl\n",
      "hashtag bot5 hashtag_washingtonpost_sliced_home_tl\n",
      "hashtag bot6 hashtag_washingtonpost_sliced_home_tl\n",
      "hashtag bot4 hashtag_washingtonpost_sliced_usr_tl\n",
      "hashtag bot5 hashtag_washingtonpost_sliced_usr_tl\n",
      "hashtag bot6 hashtag_washingtonpost_sliced_usr_tl\n",
      "hashtag bot4 hashtag_washingtonpost_sliced_friend_usr_tl\n",
      "hashtag bot5 hashtag_washingtonpost_sliced_friend_usr_tl\n",
      "hashtag bot6 hashtag_washingtonpost_sliced_friend_usr_tl\n",
      "url bot7 url_USATODAY_sliced_home_tl\n",
      "url bot8 url_USATODAY_sliced_home_tl\n",
      "url bot9 url_USATODAY_sliced_home_tl\n",
      "url bot7 url_USATODAY_sliced_usr_tl\n",
      "url bot8 url_USATODAY_sliced_usr_tl\n",
      "url bot9 url_USATODAY_sliced_usr_tl\n",
      "url bot7 url_USATODAY_sliced_friend_usr_tl\n",
      "url bot8 url_USATODAY_sliced_friend_usr_tl\n",
      "url bot9 url_USATODAY_sliced_friend_usr_tl\n",
      "hashtag bot7 hashtag_USATODAY_sliced_home_tl\n",
      "hashtag bot8 hashtag_USATODAY_sliced_home_tl\n",
      "hashtag bot9 hashtag_USATODAY_sliced_home_tl\n",
      "hashtag bot7 hashtag_USATODAY_sliced_usr_tl\n",
      "hashtag bot8 hashtag_USATODAY_sliced_usr_tl\n",
      "hashtag bot9 hashtag_USATODAY_sliced_usr_tl\n",
      "hashtag bot7 hashtag_USATODAY_sliced_friend_usr_tl\n",
      "hashtag bot8 hashtag_USATODAY_sliced_friend_usr_tl\n",
      "hashtag bot9 hashtag_USATODAY_sliced_friend_usr_tl\n",
      "url bot10 url_WSJ_sliced_home_tl\n",
      "url bot11 url_WSJ_sliced_home_tl\n",
      "url bot12 url_WSJ_sliced_home_tl\n",
      "url bot10 url_WSJ_sliced_usr_tl\n",
      "url bot11 url_WSJ_sliced_usr_tl\n",
      "url bot12 url_WSJ_sliced_usr_tl\n",
      "url bot10 url_WSJ_sliced_friend_usr_tl\n",
      "url bot11 url_WSJ_sliced_friend_usr_tl\n",
      "url bot12 url_WSJ_sliced_friend_usr_tl\n",
      "hashtag bot10 hashtag_WSJ_sliced_home_tl\n",
      "hashtag bot11 hashtag_WSJ_sliced_home_tl\n",
      "hashtag bot12 hashtag_WSJ_sliced_home_tl\n",
      "hashtag bot10 hashtag_WSJ_sliced_usr_tl\n",
      "hashtag bot11 hashtag_WSJ_sliced_usr_tl\n",
      "hashtag bot12 hashtag_WSJ_sliced_usr_tl\n",
      "hashtag bot10 hashtag_WSJ_sliced_friend_usr_tl\n",
      "hashtag bot11 hashtag_WSJ_sliced_friend_usr_tl\n",
      "hashtag bot12 hashtag_WSJ_sliced_friend_usr_tl\n",
      "url bot14 url_BreitbartNews_sliced_home_tl\n",
      "url bot15 url_BreitbartNews_sliced_home_tl\n",
      "url bot13 url_BreitbartNews_sliced_home_tl\n",
      "url bot14 url_BreitbartNews_sliced_usr_tl\n",
      "url bot15 url_BreitbartNews_sliced_usr_tl\n",
      "url bot13 url_BreitbartNews_sliced_usr_tl\n",
      "url bot14 url_BreitbartNews_sliced_friend_usr_tl\n",
      "url bot15 url_BreitbartNews_sliced_friend_usr_tl\n",
      "url bot13 url_BreitbartNews_sliced_friend_usr_tl\n",
      "hashtag bot14 hashtag_BreitbartNews_sliced_home_tl\n",
      "hashtag bot15 hashtag_BreitbartNews_sliced_home_tl\n",
      "hashtag bot13 hashtag_BreitbartNews_sliced_home_tl\n",
      "hashtag bot14 hashtag_BreitbartNews_sliced_usr_tl\n",
      "hashtag bot15 hashtag_BreitbartNews_sliced_usr_tl\n",
      "hashtag bot13 hashtag_BreitbartNews_sliced_usr_tl\n",
      "hashtag bot14 hashtag_BreitbartNews_sliced_friend_usr_tl\n",
      "hashtag bot15 hashtag_BreitbartNews_sliced_friend_usr_tl\n",
      "hashtag bot13 hashtag_BreitbartNews_sliced_friend_usr_tl\n"
     ]
    }
   ],
   "source": [
    "ts_helper.generate_all_time_series(\n",
    "    db_conn=db_helper.connect_db(), \n",
    "    INIT_SEED_MAP=INIT_SEED_MAP, \n",
    "    bots_mask=BOTS_RENAME\n",
    ")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
